﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using FX.CC.Andon.Entity.ReportManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.ReportManage
{
    /// <summary>
    /// 异常情况报表数据库
    /// </summary>
    public class AbnormalReptService : DbHelperBase
    {
        public int GetAbnormalReptCount(AbnormalQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) [count] FROM UT_EventRec AS B   ");
            strSql.Append(" INNER JOIN UT_Equipment AS C ON B.EqID=C.EqID ");
            strSql.Append(" INNER JOIN UT_Line AS M ON C.LineID=M.LineID ");
            strSql.Append(" INNER JOIN UT_EType AS D ON B.ETypeCode=D.ETypeCode ");
            strSql.Append(" INNER JOIN UT_ETypeDetail AS F ON B.DetailID=F.DetailID ");
            strSql.Append(" LEFT JOIN UT_User AS G ON B.StartUserID=G.UserID ");
            strSql.Append(" LEFT JOIN UT_User AS H ON B.EndUserID=H.UserID ");
            strSql.Append(" LEFT JOIN UT_PrdTask AS J ON B.TaskID=J.TaskID ");
            strSql.Append("  LEFT JOIN UT_Flow AS X ON J.FlowID=X.FlowID ");
            strSql.Append(" LEFT JOIN UT_Product AS K ON X.PrdID=K.PrdID ");
            strSql.Append(" WHERE B.EndTime IS NOT NULL  ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" AND B.BizDate >=@SBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" AND B.BizDate <=@EBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqID))
            {
                strSql.Append(" and A.EqID='" + qcmodel.EqID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" and M.LineID ='" + qcmodel.LineID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.ETypeCode))
            {
                strSql.Append(" and B.ETypeCode ='" + qcmodel.ETypeCode + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" and K.PrdID ='" + qcmodel.PrdID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.IsCancle))
            {
                strSql.Append(" and B.IsCancle =@IsCancle ");
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateEnd));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.IsCancle))
                    {
                        Db.AddInParameter(cmd, "@IsCancle", qcmodel.IsCancle);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<AbnormalEntity> GetAbnormalReptList(AbnormalQcEntity qcmodel)
        {
            var list = new List<AbnormalEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by B.StartTime DESC ) AS idx,C.EqCode,C.EqName,M.LineCode,M.LineName,B.StartTime,B.EndTime,datediff(second,B.StartTime,B.EndTime) as ElapsedTime,B.ReduceMin,B.EventID,D.ETypeCode,D.ETypeName,F.DetailCode,F.DetailName,G.FullName as StartUserFullName,H.FullName as EndUserFullName,B.Remark,K.PrdCode,K.PrdName,B.IsCancle FROM  UT_EventRec AS B   ");
            strSql.Append(" INNER JOIN UT_Equipment AS C ON B.EqID=C.EqID ");
            strSql.Append(" INNER JOIN UT_Line AS M ON C.LineID=M.LineID ");
            strSql.Append(" INNER JOIN UT_EType AS D ON B.ETypeCode=D.ETypeCode ");
            strSql.Append(" INNER JOIN UT_ETypeDetail AS F ON B.DetailID=F.DetailID ");
            strSql.Append(" LEFT JOIN UT_User AS G ON B.StartUserID=G.UserID ");
            strSql.Append(" LEFT JOIN UT_User AS H ON B.EndUserID=H.UserID ");
            strSql.Append(" LEFT JOIN UT_PrdTask AS J ON B.TaskID=J.TaskID ");
            strSql.Append("  LEFT JOIN UT_Flow AS X ON J.FlowID=X.FlowID ");
            strSql.Append(" LEFT JOIN UT_Product AS K ON X.PrdID=K.PrdID ");
            strSql.Append(" WHERE B.EndTime IS NOT NULL  ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" AND B.BizDate >=@SBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" AND B.BizDate <=@EBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqID))
            {
                strSql.Append(" AND B.EqID='" + qcmodel.EqID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" AND M.LineID ='" + qcmodel.LineID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.ETypeCode))
            {
                strSql.Append(" and B.ETypeCode ='" + qcmodel.ETypeCode + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" and K.PrdID ='" + qcmodel.PrdID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.IsCancle))
            {
                strSql.Append(" and B.IsCancle =@IsCancle ");
            }
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateEnd));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.IsCancle))
                    {
                        Db.AddInParameter(cmd, "@IsCancle", qcmodel.IsCancle);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new AbnormalEntity();
                            if (reader["EventID"] != DBNull.Value)
                                model.EventID = ReaderHelper.GetValue<Guid>(reader, "EventID");
                            model.PrdCode = ReaderHelper.GetValue<string>(reader, "PrdCode");
                            model.PrdName = ReaderHelper.GetValue<string>(reader, "PrdName");
                            model.LineCode = ReaderHelper.GetValue<string>(reader, "LineCode");
                            model.LineName = ReaderHelper.GetValue<string>(reader, "LineName");
                            model.EqCode = ReaderHelper.GetValue<string>(reader, "EqCode");
                            model.EqName = ReaderHelper.GetValue<string>(reader, "EqName");
                            model.StartTime = ReaderHelper.GetValue<DateTime>(reader, "StartTime");
                            if (reader["EndTime"] != DBNull.Value)
                                model.EndTime = Convert.ToDateTime(reader["EndTime"]);
                            var reduceMin = 0;
                            if (reader["ReduceMin"] != DBNull.Value)
                                reduceMin = Convert.ToInt32(reader["ReduceMin"]);
                            if (reader["ElapsedTime"] != DBNull.Value)
                                model.ElapsedTime = Math.Round(Convert.ToDecimal(reader["ElapsedTime"]) / 60, 2) - reduceMin;
                            model.ETypeName = reader["ETypeName"] == obj ? null : Convert.ToString(reader["ETypeName"]);
                            model.DetailCode = reader["DetailCode"] == obj ? null : Convert.ToString(reader["DetailCode"]);
                            model.DetailName = reader["DetailName"] == obj ? null : Convert.ToString(reader["DetailName"]);
                            model.StartUserFullName = reader["StartUserFullName"] == obj ? null : Convert.ToString(reader["StartUserFullName"]);
                            model.EndUserFullName = reader["EndUserFullName"] == obj ? null : Convert.ToString(reader["EndUserFullName"]);
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                            if (reader["IsCancle"] != DBNull.Value)
                                model.IsCancle = Convert.ToBoolean(reader["IsCancle"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public IList<ExportAbnormalEntity> ExportAbnormalRept(AbnormalQcEntity qcmodel)
        {
            var list = new List<ExportAbnormalEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT B.IsCancle,K.PrdCode,K.PrdName,M.LineName,C.EqCode,C.EqName,B.StartTime,B.EndTime,datediff(second,B.StartTime,B.EndTime) as ElapsedTime,B.ReduceMin,D.ETypeName,F.DetailCode,F.DetailName,G.FullName as StartUserFullName,H.FullName as EndUserFullName,B.Remark FROM  UT_EventRec AS B  ");
            strSql.Append(" INNER JOIN UT_Equipment AS C ON B.EqID=C.EqID ");
            strSql.Append(" INNER JOIN UT_Line AS M ON C.LineID=M.LineID ");
            strSql.Append(" INNER JOIN UT_EType AS D ON B.ETypeCode=D.ETypeCode ");
            strSql.Append(" INNER JOIN UT_ETypeDetail AS F ON B.DetailID=F.DetailID ");
            strSql.Append(" LEFT JOIN UT_User AS G ON B.StartUserID=G.UserID ");
            strSql.Append(" LEFT JOIN UT_User AS H ON B.EndUserID=H.UserID ");
            strSql.Append(" LEFT JOIN UT_PrdTask AS J ON B.TaskID=J.TaskID ");
            strSql.Append(" LEFT JOIN UT_Flow AS X ON J.FlowID=X.FlowID ");
            strSql.Append(" LEFT JOIN UT_Product AS K ON X.PrdID=K.PrdID ");
            strSql.Append(" WHERE B.EndTime IS NOT NULL ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" AND B.BizDate >=@SBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" AND B.BizDate <=@EBizDate");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqID))
            {
                strSql.Append(" AND B.EqID='" + qcmodel.EqID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" AND M.LineID ='" + qcmodel.LineID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.ETypeCode))
            {
                strSql.Append(" and B.ETypeCode ='" + qcmodel.ETypeCode + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" and K.PrdID ='" + qcmodel.PrdID + "'");
            }
            if (!string.IsNullOrEmpty(qcmodel.IsCancle))
            {
                strSql.Append(" and B.IsCancle =@IsCancle ");
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        Db.AddInParameter(cmd, "@SBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateFrom));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        Db.AddInParameter(cmd, "@EBizDate", DbType.DateTime, Convert.ToDateTime(qcmodel.BizDateEnd));
                    }
                    if (!string.IsNullOrEmpty(qcmodel.IsCancle))
                    {
                        Db.AddInParameter(cmd, "@IsCancle", qcmodel.IsCancle);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {

                            var model = new ExportAbnormalEntity();
                            model.IsCancle = Convert.ToBoolean(reader["IsCancle"]) ? "已作废" : "未作废";
                            model.PrdCode = reader["PrdCode"] == obj ? null : Convert.ToString(reader["PrdCode"]);
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            model.LineName = reader["LineName"] == obj ? null : Convert.ToString(reader["LineName"]);
                            model.EqCode = reader["EqCode"] == obj ? null : Convert.ToString(reader["EqCode"]);
                            model.EqName = reader["EqName"] == obj ? null : Convert.ToString(reader["EqName"]);
                            if (reader["StartTime"] != DBNull.Value)
                                model.StartTime = Convert.ToDateTime(reader["StartTime"]);
                            if (reader["EndTime"] != DBNull.Value)
                                model.EndTime = Convert.ToDateTime(reader["EndTime"]);
                            var reduceMin = 0;
                            if (reader["ReduceMin"] != DBNull.Value)
                                reduceMin = Convert.ToInt32(reader["ReduceMin"]);
                            if (reader["ElapsedTime"] != DBNull.Value)
                                model.ElapsedTime = Math.Round(Convert.ToDecimal(reader["ElapsedTime"]) / 60, 2) - reduceMin;
                            model.ETypeName = reader["ETypeName"] == obj ? null : Convert.ToString(reader["ETypeName"]);
                            model.DetailCode = reader["DetailCode"] == obj ? null : Convert.ToString(reader["DetailCode"]);
                            model.DetailName = reader["DetailName"] == obj ? null : Convert.ToString(reader["DetailName"]);
                            model.StartUserFullName = reader["StartUserFullName"] == obj ? null : Convert.ToString(reader["StartUserFullName"]);
                            model.EndUserFullName = reader["EndUserFullName"] == obj ? null : Convert.ToString(reader["EndUserFullName"]);
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }



        public int GetAbnormalDetailCount(AbnormalQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [count] FROM UT_SignRec AS A  ");
            strSql.Append(" LEFT JOIN UT_Message AS B ON A.EventID=B.EventID  and A.UserID=b.ToUserID ");
            strSql.Append(" LEFT JOIN UT_User AS C ON A.UserID=C.UserID ");
            strSql.Append(" WHERE A.EventID=@EventID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@EventID", qcmodel.EventID);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<AbnormalDetailEntity> GetAbnormalDetailList(AbnormalQcEntity qcmodel)
        {
            var list = new List<AbnormalDetailEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by B.GenTime) AS idx,A.SignID,C.FullName AS SignUser,B.GenTime,A.SignTime,datediff(second,B.GenTime,A.SignTime)as TotalMinutes  FROM UT_SignRec AS A  ");
            strSql.Append(" LEFT JOIN UT_Message AS B ON A.EventID=B.EventID  and A.UserID=b.ToUserID ");
            strSql.Append(" LEFT JOIN UT_User AS C ON A.UserID=C.UserID ");
            strSql.Append(" WHERE A.EventID=@EventID ");
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@EventID", qcmodel.EventID);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {

                            var model = new AbnormalDetailEntity();
                            if (reader["SignID"] != DBNull.Value)
                                model.SignID = new Guid(reader["SignID"].ToString());
                            model.SignUser = reader["SignUser"] == obj ? null : Convert.ToString(reader["SignUser"]);
                            model.GenTime = reader["GenTime"] == obj ? "<span style=\"color: Red;font-weight:bold;\">暂无</span>" : string.Format("{0:yyyy-MM-dd HH:mm:ss}", reader["GenTime"]);
                            model.SignTime = reader["SignTime"] == obj ? "<span style=\"color: Red;font-weight:bold;\">暂无</span>" : string.Format("{0:yyyy-MM-dd HH:mm:ss}", reader["SignTime"]);
                            if (reader["TotalMinutes"] != DBNull.Value)
                                model.TotalMinutes = Math.Round(Convert.ToDecimal(reader["TotalMinutes"]) / 60, 2);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        #region 发信明细
        public int GetSenderDetailCount(AbnormalQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [count] FROM UT_Message AS A   ");
            strSql.Append(" INNER JOIN UT_User AS B ON A.FromUserID=B.UserID ");
            strSql.Append(" INNER JOIN UT_User AS C ON A.ToUserID=C.UserID ");
            strSql.Append(" LEFT JOIN UT_SignRec AS D ON A.EventID=D.EventID AND A.ToUserID=D.UserID ");
            strSql.Append(" WHERE A.EventID=@EventID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@EventID", qcmodel.EventID);

                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<SenderDetailEntity> GetSenderDetailList(AbnormalQcEntity qcmodel)
        {
            var list = new List<SenderDetailEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.GenTime) AS idx,A.MsgID,B.FullName AS SendUser,C.FullName AS ReceiveUser,A.GenTime,D.EventID AS IsSign FROM UT_Message AS A  ");
            strSql.Append(" INNER JOIN UT_User AS B ON A.FromUserID=B.UserID ");
            strSql.Append(" INNER JOIN UT_User AS C ON A.ToUserID=C.UserID ");
            strSql.Append(" LEFT JOIN UT_SignRec AS D ON A.EventID=D.EventID AND A.ToUserID=D.UserID ");
            strSql.Append(" WHERE A.EventID=@EventID ");
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@EventID", qcmodel.EventID);

                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new SenderDetailEntity();
                            model.MsgID = reader["MsgID"] == obj ? null : Convert.ToString(reader["MsgID"]);
                            model.SendUser = reader["SendUser"] == obj ? null : Convert.ToString(reader["SendUser"]);
                            model.ReceiveUser = reader["ReceiveUser"] == obj ? null : Convert.ToString(reader["ReceiveUser"]);
                            if (reader["GenTime"] != DBNull.Value)
                                model.GenTime = Convert.ToDateTime(reader["GenTime"]);
                            model.IsSign = reader["IsSign"] == obj ? 0 : 1;
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
        #endregion
    }
}
